#!/usr/bin/env python

import argparse
import psycopg2
import sys
import datetime
import re
import subprocess

REDMINE_IP= '10.20.12.94:3000' ## IP and port of the machine hosting redmine

## Hosts & Paths where project directories will go
projectDirs= [('10.20.12.94', '/Volumes/groups/Research/sblab/public_folders/berald01/projects'),
              ('143.65.169.175', '/data01/sblab/users/berald01/projects'),
              ('uk-cri-lcst01', '/lustre/sblab/berald01/projects'),
             ('10.20.192.25', '/data/sblab-data/berald01/projects')]
# projectDirs= [('10.20.13.11', '~/Tritume/')]

docstring= """
DESCRIPTION:
    Interactively initialize a project by creating a new project in redmine, in sblab and by
    creating project directories in different servers.
    Project directories will be created on (hostname/IP, path):
%s

USAGE:
    create_project.py

NOTES:
    Redmine projects are created by inserting a row in table redmine.projects with
    input passed interactively. Projects thus created do not have any module
    installed. To enable modules (e.g. wiki pages) go to the newly created project
    then Settings -> Modules
""" %('\n'.join([str(x) for x in projectDirs]))

if len(sys.argv) != 1:
    print(docstring)
    sys.exit()

# -----------------------------------------------------------------------------
def getMax_lft_rgt(conn, table):
    """Return the max value in columns lft and rgt in "table". That is:
    MAX(table.lft, table.rgt)
    
    ARGUMENTS:
    table:
        Table to query containing columns "lft" and "rgt" (e.g. redmine.projects)
    conn:
        Connection from psycopg2.connect() to connect to db where table lives.
        E.g.: conn = psycopg2.connect("dbname=redmine user=redmine")
    
    RETURN
        Integer.
    
    NOTE:
        Getting MAX(table.rgt) should suffice, no need to query table.lft as well!?
        For the meaning of lft and rgt see for example
        http://www.sitepoint.com/hierarchical-data-database-2/
    """
    cur= conn.cursor()
    sql= """SELECT MAX(branch) FROM (SELECT lft AS branch FROM %(t)s UNION SELECT rgt AS branch FROM %(t)s) AS t;""" %{'t': table}
    cur.execute(sql)
    maxval= cur.fetchone()[0]
    return(maxval)
    cur.close()


class BadIdentifier(Exception):
    def __init__(self, value):
           self.parameter = value

def validateIdentifier(ID):
    """ Make sure the project identifier ID is valid for sblab."""
    if re.search('\W', ID):
        raise BadIdentifier('Identifiers must be alphanumeric only')

def getContacts(conn_sblab):
    """ Query sblab.contacts to get a list of valid contacts """
    cur= conn_sblab.cursor()
    sql= 'SELECT contact_name from contacts ORDER BY contact_name'
    cur.execute(sql)
    names= [x[0] for x in cur.fetchall()]
    cur.close()
    return(names)

def getRedmineProjectIDs(redmine_conn):
    """ Returns a list of project IDs from redmine.projects.identifier
    redmine_conn:
        Connection returned by psycopg2.connect() to connect to redmine DB.
    """
    cur= redmine_conn.cursor()
    sql= 'SELECT DISTINCT identifier from projects ORDER BY identifier;'
    cur.execute(sql)
    names= [x[0] for x in cur.fetchall()]
    cur.close()
    return(names)

def getRedmineProjectNames(redmine_conn):
    """ Returns a list of project names from redmine.projects.name
    redmine_conn:
        Connection returned by psycopg2.connect() to connect to redmine DB.
    """
    cur= redmine_conn.cursor()
    sql= 'SELECT DISTINCT name from projects ORDER BY name;'
    cur.execute(sql)
    names= [x[0] for x in cur.fetchall()]
    cur.close()
    return(names)
    
# -----------------------------------------------------------------------------

class ProjectRow:
    def __init__(self):
        self.name= None
        self.description= None
        self.is_public= True
        self.identifier= None
        self.redmine_identifier= None
        self.created_on= None
        self.updated_on= None
        self.contact= None
        self.homepage= None
    def inputOrder(self):
        """Order in which variables should be entered """
        attrOrder= ('name', 'identifier', 'description', 'contact', 'is_public')
        for x in attrOrder:
            if x not in self.__dict__.keys():
                print(self.__dict__.keys())
                sys.exit("%s is not a recognized attribute in %s" %(x, self.__class__.__name__))
        return(attrOrder)
    def identifier2redmine(self):
        """ Convert identifier to a valid one for redmine"""
        redId= self.identifier.replace('_', '-').lower()
        self.redmine_identifier= redId
    def redmineProject(self, conn):
        """Returns a dictionary of column names and values suitable for
        inserting to table redmine.projects via psycopg2.
        
        conn:
            psycopg connection that will be used to get the max lft and rgt.
            """
        lft= getMax_lft_rgt(conn, table= 'projects')
        reddict= {'name': self.name,
                  'description': self.description,
                  'is_public': self.is_public,
                  'identifier': self.redmine_identifier,
                  'created_on':self.created_on,
                  'updated_on': self.updated_on,
                  'lft': lft,
                  'rgt': lft + 1}
        return(reddict)
    def getHomepage(self, redmine_ip):
        """ Produce the default homepage for this project"""
        hp= 'http://' + REDMINE_IP + '/projects/' + self.redmine_identifier
        self.homepage= hp
    def sblabProjectInsert(self, conn):
        """ Similar to redmineProjectInsert, returns a dictionary suitable for
        inserting a row in table sblab.projects"""
        linedict= {'project': self.identifier,
                   'start_date': self.created_on,
                   'contact': self.contact,
                   'description': self.description,
                   'redmine_page': self.homepage}
        return(linedict)
        
# -----------------------------------------------------------------------------
conn = psycopg2.connect("dbname=redmine user=redmine")
cur= conn.cursor()
conn_sblab= psycopg2.connect("dbname=sblab user=dberaldi")
cur_sblab= conn_sblab.cursor()
redmineProjectRow= ProjectRow()
now= datetime.datetime.now()

contacts= getContacts(conn_sblab)
redmineIdentifiers= getRedmineProjectIDs(conn)
redmineProjectNames= getRedmineProjectNames(conn)

# -----------------------------------------------------------------------------
# -----------------------------------------------------------------------------


for attr in redmineProjectRow.inputOrder():
    if attr == 'name':
        print('-'*60)
        while True:
            x= raw_input('Enter project name [required]: ')
            if x == '':
                print('Project name is required!')
            elif x in redmineProjectNames:
                print('Project name for redmine %s is already taken!')
            else:
                break
        redmineProjectRow.name= x
    elif attr == 'identifier':
        print('-'*60)
        prefix= now.strftime("%Y%m%d") + '_'
        while True:
            x= raw_input('Identifier. Only Alphanumeric and underscores. (The prefix "%s" will be prepended): ' %(prefix))
            if x == '':
                print('An identifier is required')
            else:
                try:
                    validateIdentifier(prefix + x)
                    redmineProjectRow.identifier= prefix + x
                    redmineProjectRow.identifier2redmine()
                    if redmineProjectRow.redmine_identifier in redmineIdentifiers:
                        print('Project identifier for redmine %s is already taken!' %(redmineProjectRow.redmine_identifier))
                    else:
                        break
                except BadIdentifier:
                    print('Invalid identifier: %s. Must contain alphanumeric only!' %(x))            
        print('    Redmine project identifier will be:      %s' %(redmineProjectRow.redmine_identifier))
        print('    sblab/django project identifier will be: %s' %(redmineProjectRow.identifier))
    elif attr == 'description':
        print('-'*60)
        while True:
            x= raw_input('Description [required]: ')
            if x == '':
                print('Some description is required!')
            else:
                break
        redmineProjectRow.description= x
    elif attr == 'contact':
        print('-'*60)
        while True:
            x= raw_input('Contact/Owner of this project [required]: ')
            if x == '':
                print('A contact name is required!')
            elif x not in contacts:
                print('Name %s not found in the list of known contacts!' %(x))
                print('\nValid contact names are:')
                for n in contacts:
                    print('    ' + n)
            else:
                break
        redmineProjectRow.contact= x
    elif attr == 'is_public':
        print('-'*60)
        x= raw_input('Is this project public [Yes/No default Yes]? ')
        if x == '':
            x= True
        elif x.lower() in ('yes', 'y'):
            x= True
        else:
            x= False
        redmineProjectRow.is_public= x
    else:
        sys.exit('Invalid attribute: %s' %(attr))

redmineProjectRow.created_on= now
redmineProjectRow.updated_on= now
redmineProjectRow.getHomepage(REDMINE_IP)

""" Insert row to redmine.projects """
redmineRow= redmineProjectRow.redmineProject(conn)
colnames= redmineRow.keys()
colvalues= [redmineRow[x] for x in colnames]

sql= 'INSERT INTO projects (%s) VALUES (%s);' %(', '.join(colnames), ', '.join(['%s'] * len(colvalues)))
#print(cur.mogrify(sql, colvalues))
cur.execute(sql, colvalues)

""" Insert into sblab.projects"""
sblabRow= redmineProjectRow.sblabProjectInsert(conn_sblab)
colnames= sblabRow.keys()
colvalues= [sblabRow[x] for x in colnames]

sql= 'INSERT INTO projects (%s) VALUES (%s);' %(', '.join(colnames), ', '.join(['%s'] * len(colvalues)))
# print(cur_sblab.mogrify(sql, colvalues))
cur_sblab.execute(sql, colvalues)

""" Create project directories """
print('-'*60)
for host, path in projectDirs:
    cmd= 'ssh %s "mkdir -p %s/%s/Documents"' %(host, path, redmineProjectRow.identifier)
    print('Creating directory:\n%s\nPress enter to continue' %(cmd))
    raw_input()
    p= subprocess.Popen(cmd, shell= True, stderr= subprocess.PIPE)
    stdout, stderr= p.communicate()
    print(stderr)

#attrs= vars(redmineProjectRow)
#print(attrs)

# Commit/Rollback changes
cur.close()
conn.commit()
conn.close()

cur_sblab.close()
conn_sblab.commit()
conn_sblab.close()
sys.exit()
